package com.ycd.dao.Impl;

import com.ycd.dao.UserEntityDao;
import com.ycd.Entity.UserEntity;
import com.ycd.Util.jdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class UserEntityImpl implements UserEntityDao {

    /*
     * 添加用户
     * */
    @Override
    public void addUser(UserEntity userentity) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = jdbcUtil.getCon();
            String sql = "insert into sys_user (name,acct,pwd,crtime,uptime) values(name,?,?,now(),now())";
            stmt = conn.prepareStatement(sql);

            stmt.setString(1, userentity.getAcct());
            stmt.setString(2, userentity.getPwd());
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.closeAll(conn, stmt, null);
        }
    }

    /**
     * 修改用户
     * @param userentity
     */
    @Override
    public void updateUser(UserEntity userentity) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = jdbcUtil.getCon();
            String sql = "update sys_user set  name=?,pwd=?  where id=?";
            stmt = conn.prepareStatement(sql);

            stmt.setString(1, userentity.getName());
            stmt.setString(2, userentity.getPwd());
            stmt.setInt(3, userentity.getId());

             stmt.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            jdbcUtil.closeAll(conn, stmt, null);
        }
    }


    /**
     * 删除用户
     *
     * @param id
     */
    @Override
    public void delUser(int id) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = jdbcUtil.getCon();
            String sql = "delete from sys_user where id= ?";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, id);

            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            jdbcUtil.closeAll(conn,stmt,null);
        }

    }


    /*
     * 查询所有用户
     * */
    @Override
    public List<UserEntity> queryAll() {
        List<UserEntity> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = jdbcUtil.getCon();
            String sql = "select id,name,acct,pwd,crtime,uptime from sys_user order by crtime  desc";
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            while (rs.next()) {
                UserEntity userentity = new UserEntity();
                userentity.setId(rs.getInt("id"));
                userentity.setName(rs.getString("name"));
                userentity.setAcct(rs.getString("acct"));
                userentity.setPwd(rs.getString("pwd"));
                userentity.setCrTime(rs.getDate("crtime"));

                list.add(userentity);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.closeAll(conn, stmt, rs);
        }
        return list;
    }







    /*
     * 根据ID查找用户
     * */
    @Override
    public UserEntity getUserById(int id) {
        UserEntity userentity=null;

        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = jdbcUtil.getCon();
            String sql = "select id,name,acct,pwd,crtime,uptime from sys_user where id=?";
            stmt=conn.prepareStatement(sql);

            stmt.setInt(1,id);
            rs=stmt.executeQuery();
            if(rs.next()){
                userentity=new UserEntity();
                userentity.setId(rs.getInt("id"));
                userentity.setName(rs.getString("name"));
                userentity.setAcct(rs.getString("acct"));
                userentity.setPwd(rs.getString("pwd"));
                userentity.setCrTime(rs.getDate("crtime"));
                userentity.setUpTime(rs.getDate("uptime"));

            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            jdbcUtil.closeAll(conn,stmt,rs);
        }
        return userentity;
    }

    @Override
    public UserEntity getUserByAcct(String acct) {
        UserEntity user = null;
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = jdbcUtil.getCon();
            String sql = "select id,name,acct,pwd,crtime,uptime from sys_user where acct =?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, acct);
            rs = stmt.executeQuery();
            while (rs.next()) {
                user = new UserEntity();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setAcct(rs.getString("acct"));
                user.setPwd(rs.getString("pwd"));
                user.setCrTime(rs.getDate("crtime"));
                user.setUpTime(rs.getDate("uptime"));

            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.closeAll(conn, stmt, rs);
        }
        return user;
    }

    @Override
    public boolean getAddReg(String acct) {
        String sql = "select * from sys_user where acct=?";
        Connection conn = jdbcUtil.getCon();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, acct);
            rs=stmt.executeQuery();
            return rs.next();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            jdbcUtil.closeAll(conn, stmt, rs);
        }
        return false;
    }

    @Override
    public List<Map<String, String>> queryUser() {
        String sql="select id,name,acct,pwd,crtime,uptime from sys_user";
        return  jdbcUtil.query(sql,new Object[]{});

    }

    @Override
    public List<UserEntity> queryAll(int offset, int pageSize, UserEntity user) {
        List<UserEntity> list=new ArrayList<>();
        Connection conn=null;
        PreparedStatement stmt=null;
        ResultSet rs=null;
        try {
            conn = jdbcUtil.getCon();
            String sql="select id,name,acct,pwd,crtime,uptime from sys_user";
            if(user!=null && user.getName() !=null){
                sql+="where name like ?";
            }
            sql+=" limit ?,?";

            stmt=conn.prepareStatement(sql);

            if(user!=null && user.getName() !=null){
                stmt.setString(1,"%"+user.getName()+"%");
                stmt.setInt(2,offset);
                stmt.setInt(3,pageSize);
            }else{
                stmt.setInt(1,offset);
                stmt.setInt(2,pageSize);
            }


            rs=stmt.executeQuery();
            while(rs.next()){
                UserEntity sysUser=new UserEntity();
                sysUser.setId(rs.getInt("id"));
                sysUser.setName(rs.getString("name"));
                sysUser.setAcct(rs.getString("acct"));
                sysUser.setPwd(rs.getString("pwd"));
                sysUser.setCrTime(rs.getDate("crtime"));
                sysUser.setUpTime(rs.getDate("uptime"));
                list.add(sysUser);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            jdbcUtil.closeAll(conn,stmt,rs);
        }
        return list;
    }

    @Override
    public int getAllCont() {
        Connection conn=null;
        PreparedStatement stmt=null;
        ResultSet rs=null;
        try {
            conn = jdbcUtil.getCon();
            String sql="select count(1)  from sys_user ";
            stmt=conn.prepareStatement(sql);

            rs=stmt.executeQuery();
            if(rs.next()){
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            jdbcUtil.closeAll(conn,stmt,rs);
        }
        return 0;
    }


}
